package com.data;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;

import java.sql.*;

/**
 * @author sergiu.marin
 * @since 26.01.2014
 */
@EnableScheduling
public class TransferDataTask {

    private static final Logger log = Logger.getLogger(DBUtils.class);

    @Autowired
    private ApplicationConfiguration applicationConfiguration;

    //@Scheduled(fixedDelay = 300000)
    @Scheduled(fixedDelay = 5000)
    public void executeTask() {
        log.info("### Transfer data task started at: " + new java.util.Date());
        // get connection to change_tracker_db
        Connection changesDBConn = DBUtils.getConnection(
                applicationConfiguration.getProperty("change.tracker.db.driver.name"),
                applicationConfiguration.getProperty("change.tracker.db.url"),
                Integer.valueOf(applicationConfiguration.getProperty("change.tracker.db.port")),
                applicationConfiguration.getProperty("change.tracker.db.name"),
                applicationConfiguration.getProperty("change.tracker.db.username"),
                applicationConfiguration.getProperty("change.tracker.db.password"));
        // get connection to the original db
        Connection originalDBConn = DBUtils.getConnection(
                        applicationConfiguration.getProperty("original.db.driver.name"),
                        applicationConfiguration.getProperty("original.db.url"),
                        Integer.valueOf(applicationConfiguration.getProperty("original.db.port")),
                        applicationConfiguration.getProperty("original.db.name"),
                        applicationConfiguration.getProperty("original.db.username"),
                        applicationConfiguration.getProperty("original.db.password"));
        // get connection to the copy of original external db
        Connection copyDBConn = DBUtils.getConnection(
                                applicationConfiguration.getProperty("copy.db.driver.name"),
                                applicationConfiguration.getProperty("copy.db.url"),
                                Integer.valueOf(applicationConfiguration.getProperty("copy.db.port")),
                                applicationConfiguration.getProperty("copy.db.name"),
                                applicationConfiguration.getProperty("copy.db.username"),
                                applicationConfiguration.getProperty("copy.db.password"));

        String sql = "SELECT c.*, t.table_name, t.pk_column  FROM table_row_changes AS c JOIN db_tables AS t ON t.id=c.table_id WHERE changed=true LIMIT 100";
        try {

            Statement statement = changesDBConn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                transferData(rs.getString("table_name"), rs.getInt("row_id"), rs.getString("pk_column"), originalDBConn, copyDBConn);

                PreparedStatement ps = changesDBConn.prepareStatement("UPDATE table_row_changes set changed=false WHERE id=?");
                ps.setInt(1, rs.getInt("id"));
                ps.executeUpdate();
            }

            log.info("### Transfer data task successfully finished at: " + new java.util.Date());

        } catch (SQLException e) {
            log.error("An error has occurred: " + e.getMessage());
        } finally {
            try {
                if (changesDBConn != null) {
                    changesDBConn.close();
                }
                if (copyDBConn != null) {
                    copyDBConn.close();
                }
                if (originalDBConn != null) {
                    originalDBConn.close();
                }
            } catch (SQLException e) {
                log.error("An error has occurred: " + e.getMessage());
            }
        }
    }

    private static void transferData(String tableName, int rowId, String primaryCol, Connection originalDBConn, Connection copyDBConn) throws SQLException {

        String originalDBTableQuery = "SELECT * FROM " + tableName + " WHERE " + primaryCol + "=?";
        String copyDBDelete = "DELETE FROM " + tableName + " WHERE " + primaryCol + "=?";
        String copyDBInsert = "INSERT INTO " + tableName + " VALUES(";
        PreparedStatement ps = originalDBConn.prepareStatement(originalDBTableQuery);
        ps.setInt(1, rowId);
        ResultSet originalDBTableData = ps.executeQuery();

        ps = copyDBConn.prepareStatement(copyDBDelete);
        ps.setInt(1, rowId);
        ps.executeUpdate();

        for (int i = 1; i <= originalDBTableData.getMetaData().getColumnCount(); i++) {
            if (i != 1) {
                copyDBInsert += ", ";
            }
            copyDBInsert += " ?";
        }
        copyDBInsert += ")";

        ps = copyDBConn.prepareStatement(copyDBInsert);

        if (originalDBTableData.next()) {
            for (int i = 1; i <= originalDBTableData.getMetaData().getColumnCount(); i++) {

                if (originalDBTableData.getObject(i) instanceof String) {
                    ps.setString(i, originalDBTableData.getString(i));
                } else if (originalDBTableData.getObject(i) instanceof Integer) {
                    ps.setInt(i, originalDBTableData.getInt(i));
                } else if (originalDBTableData.getObject(i) instanceof Double) {
                    ps.setDouble(i, originalDBTableData.getDouble(i));
                } else if (originalDBTableData.getObject(i) instanceof Long) {
                    ps.setLong(i, originalDBTableData.getLong(i));
                } else if (originalDBTableData.getObject(i) instanceof Date) {
                    ps.setDate(i, originalDBTableData.getDate(i));
                } else if (originalDBTableData.getObject(i) instanceof Boolean) {
                    ps.setBoolean(i, originalDBTableData.getBoolean(i));
                } else if (originalDBTableData.getObject(i) instanceof byte[]) {
                    ps.setBlob(i, originalDBTableData.getBlob(i));
                } else {
                    ps.setObject(i, originalDBTableData.getClob(i));
                }
            }
        }
        ps.executeUpdate();


    }

}
